MySQLでビューを使う

#初心者向け

#データベース

takahara

2016.01.27

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

MySQLの理解を深めるために調査した結果を記事にします。
今回はビューを使用し、想定する状況に対応する例を考えてみました。

想定する状況

・本屋（２店舗）に関する情報を管理している。
・テーブルは３つ。
・依頼に応じたデータを提出する必要がある。
・関連するテーブルの内容を手間無く取得できる様にしておきたい。

テーブル

stores（店舗ごとの在庫）

id=行番号、store_id=店番号、item_id=商品番号、items=在庫数

+----+----------+---------+-------+
| id | store_id | item_id | items |
+----+----------+---------+-------+
|  1 |        1 |       1 |    10 |
|  2 |        1 |       2 |    30 |
|  3 |        1 |       3 |   100 |
|  4 |        2 |       1 |    15 |
|  5 |        2 |       4 |    25 |
|  6 |        2 |       5 |    15 |
|  7 |        2 |       3 |   120 |
+----+----------+---------+-------+

store_info（店舗情報）

store_id=店番号、store_name=店名、area=地区

+----------+------------+-------+
| store_id | store_name | area  |
+----------+------------+-------+
|        1 | store_A    | tokyo |
|        2 | store_B    | osaka |
+----------+------------+-------+

item_info（商品情報）

item_id=商品番号、item_name=商品名、price=商品価格、genre=種別

+---------+-----------+-------+----------+
| item_id | item_name | price | genre    |
+---------+-----------+-------+----------+
|       1 | book1     |   500 | novel    |
|       2 | book2     |  1200 | novel    |
|       3 | book3     |   200 | magazine |
|       4 | book4     |   800 | magazine |
|       5 | book5     |   750 | cook     |
|       6 | book6     |  3000 | art      |
+---------+-----------+-------+----------+

テーブルをビューにまとめる

３つのテーブルを１つにまとめます。

SQL文

CREATE VIEW
  view1
  (  
    store_id, 
    item_id, 
    items, 
    item_name, 
    price, 
    genre,
    store_name,
    area 
  )
AS
  SELECT
    T.store_id, 
    T.item_id, 
    T.items, 
    I.item_name, 
    I.price, 
    I.genre,
    S.store_name,
    S.area 
  FROM
    stores AS T 
    LEFT JOIN 
    item_info AS I 
    ON
    T.item_id=I.item_id
      LEFT JOIN
      store_info AS S
      ON
      T.store_id=S.store_id;

実行結果

テーブルと同じ様に扱えるので今後の作業が楽になります。

mysql> select * from view1;
+----------+---------+-------+-----------+-------+----------+------------+-------+
| store_id | item_id | items | item_name | price | genre    | store_name | area  |
+----------+---------+-------+-----------+-------+----------+------------+-------+
|        1 |       1 |    10 | book1     |   500 | novel    | store_A    | tokyo |
|        1 |       2 |    30 | book2     |  1200 | novel    | store_A    | tokyo |
|        1 |       3 |   100 | book3     |   200 | magazine | store_A    | tokyo |
|        2 |       1 |    15 | book1     |   500 | novel    | store_B    | osaka |
|        2 |       4 |    25 | book4     |   800 | magazine | store_B    | osaka |
|        2 |       5 |    15 | book5     |   750 | cook     | store_B    | osaka |
|        2 |       3 |   120 | book3     |   200 | magazine | store_B    | osaka |
+----------+---------+-------+-----------+-------+----------+------------+-------+

テーブル確認

ビュー「view1」が含まれています。

mysql> show tables from blog;
+----------------+
| Tables_in_blog |
+----------------+
| item_info      |
| store_info     |
| stores         |
| view1          |
+----------------+

解説

構文

CREATE VIEW "ビュー名" ("列名１","列名２",...) AS <SELECT文>

２行目でビューの名前、
４〜１１行目でビューの列名を決めています。

CREATE VIEW
  view1
  (  
    store_id, 
    item_id, 
    items, 
    item_name, 
    price, 
    genre,
    store_name,
    area 
  )
AS

値を抜き出す列を指定しています。
上から順に４〜１１行目で設定した列に値が割り振られます。

  SELECT
    SS.store_id, 
    SS.item_id, 
    SS.items, 
    I.item_name, 
    I.price, 
    I.genre,
    S.store_name,
    S.area

２４〜２８行目で最初のテーブル結合設定、
２９〜３２行目で最初のテーブル結合の結果に次のテーブル結合を設定しています。

  FROM
    stores AS SS 
    LEFT JOIN 
    item_info AS I 
    ON
    SS.item_id=I.item_id
      LEFT JOIN
      store_info AS S
      ON
      SS.store_id=S.store_id;

テーブル結合についてはMySQLでテーブル結合で解説しています。